import sys

from openpyxl import load_workbook
import pandas as pd

# 加载工作簿
wb = load_workbook(filename=r'销售订单模板表0531.xlsx',
                   data_only=True)

# 获取第一个工作表
ws = wb.worksheets[0]  # 或者使用wb['Sheet1']如果你知道工作表名称
wb.close()


def get_tables(ws):
    s_e_index = {'start': [], 'end': []}
    for index, row in enumerate(ws.iter_rows()):
        cell_value = row[1].value
        if cell_value == '产品系列':
            s_e_index['start'].append(index)
        if len(s_e_index['end']) == len(s_e_index['start']) - 1 and index > s_e_index['start'][-1] and cell_value in [
            '订货明细', '订单政策', '合计',
            '政策比例：', '产品系列',
            None]:
            s_e_index['end'].append(index - 1)
    print(s_e_index)
    # ======================================================================================================================
    xlsx = pd.ExcelFile(r'F:\pythonProject2\pythonProject\Automatic_conversion\销售订单模板表0531.xlsx')
    # xlsx = '销售订单模板表0531.xlsx'
    # 获取工作表的总行数
    sheet_info = pd.read_excel(xlsx, sheet_name=0, nrows=0)
    total_rows = sheet_info.shape[0]
    # 根据找到的起始位置确定每张表的边界
    table_boundaries = {
        '订货明细': (s_e_index['start'][0], s_e_index['end'][0]),
        '订单政策': (s_e_index['start'][1], s_e_index['end'][1]),
        '宣赠品': (s_e_index['start'][2], s_e_index['end'][2])
    }
    # 读取每张表的数据
    tables = {}
    for table_name, (start, end) in table_boundaries.items():
        # 读取从start行到end行之间的数据
        tables[table_name] = pd.read_excel(xlsx, sheet_name=0, skiprows=start, nrows=end - start, usecols='B:I',
                                           dtype=str)
        # print(tables[table_name])
    # 打印每张表的数据

    # 关闭Excel文件
    xlsx.close()
    return tables


# 找到三张表的起始
# start_end_index = {'订货明细': [], '订单政策': [], '宣赠品': []}


# 找到客户名称
def find_customer(ws):
    result = []
    found = False
    # 遍历工作表中的单元格
    for row in ws.iter_rows():
        for cell in row:
            # 打印单元格的坐标和内容
            if cell.value is not None:
                if cell.value == '业务员：':
                    found = True
                    break
                result.append(cell.value)
        if found == True:
            return result[-1]


def get_data(ws):
    result = []
    tables = get_tables(ws)
    for table_name, table in tables.items():
        if table_name == '订货明细':
            for index, row in table.iterrows():
                result_dict = {}
                result_dict['产品编码'] = row['产品编码']
                result_dict['销售单价'] = row['供货价']
                result_dict['数量'] = row['数量']
                result_dict['是否赠品'] = '否'
                result.append(result_dict)
        if table_name == '订单政策':
            for index, row in table.iterrows():
                result_dict = {}
                result_dict['产品编码'] = row['产品编码']
                result_dict['销售单价'] = row['单价']
                result_dict['数量'] = row['数量']
                result_dict['是否赠品'] = '是'
                result.append(result_dict)
        if table_name == '宣赠品':
            for index, row in table.iterrows():
                result_dict = {}
                result_dict['产品编码'] = row['产品编码']
                result_dict['销售单价'] = row['单价']
                result_dict['数量'] = row['数量']
                result_dict['是否赠品'] = '是'
                result.append(result_dict)
    return result


get_data(ws)
